Combining Aggregate Functions with Conditional Logic in MySQL

Course: MySQL Training Course

Topic: Combining Aggregate Functions with Conditional Logic

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to Aggregate Functions with Conditional Logic

In MySQL, aggregate functions perform calculations on multiple rows and return a single result or one result for each group. Common aggregate functions include SUM(), COUNT(), AVG(), MIN(), and MAX().

Conditional logic allows a query to make decisions based on specified conditions. MySQL provides expressions such as CASE and IF() to return different values depending on whether a condition is satisfied.

Combining aggregate functions with conditional logic is known as conditional aggregation. It helps calculate totals, counts, averages, and other summaries for selected categories of records without requiring a separate query for every condition.

For example, a sales report can display total sales, completed sales, pending sales, and cancelled sales in a single query. Similarly, an employee report can calculate the number of employees in different salary ranges.

1.1 Learning Objectives

• Understand aggregate functions and conditional expressions in MySQL.
• Learn how to use CASE inside SUM(), COUNT(), and AVG().
• Use IF() for simple conditional calculations.
• Combine conditional aggregation with GROUP BY and HAVING.
• Generate reports that summarize multiple categories in one query.
• Handle NULL values and avoid common conditional aggregation errors.

2. Understanding Aggregate Functions in MySQL

Aggregate functions process a collection of values and return a summary. They are frequently used in reports, dashboards, financial calculations, and business analysis.

2.1 Example: Using Aggregate Functions

Consider a table named orders containing order amounts and statuses.

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_order_amount,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders;

This query returns the number of orders, total sales, average order amount, smallest order amount, and largest order amount for the rows included in the query.

3. Understanding Conditional Logic

Conditional logic evaluates a condition and returns a value depending on the result. In MySQL, CASE and IF() are commonly used inside aggregate functions.

3.1 The CASE Expression

The CASE expression evaluates conditions and returns the result associated with the first matching condition.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

If no condition matches and an ELSE branch is omitted, the expression returns NULL.

Example:

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 10000 THEN 'High Value'
        WHEN amount >= 5000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS order_category
FROM orders;

This query classifies each order based on its amount. The same conditional expression can also be placed inside an aggregate function to summarize particular categories.

3.2 The IF() Function

The MySQL IF() function returns one value when a condition is true and another when it is false.

Syntax:

IF(condition, value_if_true, value_if_false)

Example:

SELECT
    order_id,
    IF(amount >= 5000, 'Large Order', 'Small Order') AS order_type
FROM orders;

IF() is convenient for simple two-outcome conditions. For multiple categories or more complex conditions, CASE is generally easier to read.

4. What Is Conditional Aggregation?

Conditional aggregation combines an aggregate function with a conditional expression. The condition determines which values contribute to the calculation.

For example, the expression below calculates sales only for completed orders:

SELECT
    SUM(
        CASE
            WHEN status = 'Completed' THEN amount
            ELSE 0
        END
    ) AS completed_sales
FROM orders;

The CASE expression returns the order amount when the status is Completed. For other orders, it returns zero. The SUM() function adds the resulting values.

4.1 General Syntax

SELECT
    aggregate_function(
        CASE
            WHEN condition THEN expression
            ELSE default_value
        END
    ) AS result
FROM table_name;

4.2 Why Conditional Aggregation Is Useful

• Calculates multiple conditional totals in a single query.
• Reduces the need for separate queries for each category.
• Creates useful summary reports for business analysis.
• Works with grouped data, such as sales by region or employees by department.
• Can transform category values into separate report columns.
• Makes it easier to compare completed, pending, and cancelled transactions.

5. Creating a Sample Table for Practice

For the examples in these notes, create a sample table called orders.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    region VARCHAR(50),
    amount DECIMAL(10,2),
    status VARCHAR(30)
);

5.1 Inserting Sample Records

INSERT INTO orders
(order_id, customer_name, region, amount, status)
VALUES
(1, 'Amit', 'North', 12000.00, 'Completed'),
(2, 'Priya', 'South', 7500.00, 'Pending'),
(3, 'Rahul', 'North', 4500.00, 'Completed'),
(4, 'Neha', 'West', 9000.00, 'Cancelled'),
(5, 'Vikas', 'South', 15000.00, 'Completed'),
(6, 'Sneha', 'West', 3000.00, 'Pending'),
(7, 'Arjun', 'North', 8000.00, 'Pending'),
(8, 'Meera', 'South', 6000.00, 'Cancelled'),
(9, 'Karan', 'West', 11000.00, 'Completed'),
(10, 'Isha', 'North', 5000.00, 'Cancelled');

These records contain different order amounts, regions, and statuses. They can be used to practice counting records, calculating conditional totals, comparing categories, and generating grouped reports.

5.2 Viewing the Sample Data

SELECT *
FROM orders
ORDER BY order_id;

6. Combining SUM() with Conditional Logic

The SUM() function adds numeric values. When combined with CASE, it can add values only when a particular condition is satisfied.

6.1 Calculating Sales for Completed Orders

SELECT
    SUM(
        CASE
            WHEN status = 'Completed' THEN amount
            ELSE 0
        END
    ) AS completed_sales
FROM orders;

Expected result: 42,500.00

Only the amounts for Completed orders are added: 12,000 + 4,500 + 15,000 + 11,000.

6.2 Calculating Sales for Multiple Statuses

Several conditional totals can be calculated in one query by using multiple SUM(CASE ...) expressions.

SELECT
    SUM(amount) AS total_sales,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales,
    SUM(CASE
        WHEN status = 'Pending' THEN amount
        ELSE 0
    END) AS pending_sales,
    SUM(CASE
        WHEN status = 'Cancelled' THEN amount
        ELSE 0
    END) AS cancelled_sales
FROM orders;

Expected result:

This report presents all three status totals alongside total sales without requiring three separate queries.

6.3 Calculating Sales Above a Threshold

Conditional aggregation can also select values using numeric comparisons.

SELECT
    SUM(CASE
        WHEN amount >= 10000 THEN amount
        ELSE 0
    END) AS high_value_sales
FROM orders;

This query adds only amounts that are at least 10,000.00. The result is 38,000.00.

7. Combining COUNT() with Conditional Logic

The COUNT() function counts rows or non-NULL expressions. Combining it with CASE allows you to count records that satisfy a condition.

7.1 Counting Completed Orders

SELECT
    COUNT(
        CASE
            WHEN status = 'Completed' THEN 1
        END
    ) AS completed_orders
FROM orders;

Expected result: 4

When the condition is true, CASE returns 1. Otherwise, it returns NULL because no ELSE branch is provided. Since COUNT(expression) ignores NULL values, only completed orders are counted.

7.2 Counting Orders by Status

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders,
    COUNT(CASE
        WHEN status = 'Pending' THEN 1
    END) AS pending_orders,
    COUNT(CASE
        WHEN status = 'Cancelled' THEN 1
    END) AS cancelled_orders
FROM orders;

Expected result:

7.3 Alternative Using SUM()

Another common technique is to return 1 for matching records and 0 for non-matching records, then add the results.

SELECT
    SUM(CASE
        WHEN status = 'Completed' THEN 1
        ELSE 0
    END) AS completed_orders
FROM orders;

Both methods can produce the same count. With COUNT(CASE ...), omitting ELSE is important when you want non-matching records to become NULL and not be counted.

8. Combining AVG() with Conditional Logic

The AVG() function calculates the average of non-NULL values. When combined with CASE, it can calculate the average for records that meet a condition.

8.1 Average Amount for Completed Orders

SELECT
    AVG(CASE
        WHEN status = 'Completed' THEN amount
    END) AS average_completed_order
FROM orders;

Expected result: 10,625.00

Only completed order amounts are returned by the conditional expression. Other rows produce NULL and are ignored by AVG().

8.2 Comparing Average Amounts by Status

SELECT
    AVG(CASE
        WHEN status = 'Completed' THEN amount
    END) AS avg_completed,
    AVG(CASE
        WHEN status = 'Pending' THEN amount
    END) AS avg_pending,
    AVG(CASE
        WHEN status = 'Cancelled' THEN amount
    END) AS avg_cancelled
FROM orders;

This query returns the average order amount for each status in separate columns.

8.3 Understanding NULL in Conditional Averages

For conditional averages, omitting ELSE 0 is often intentional. It ensures that non-matching rows are excluded from both the sum and the count used to calculate the average.

-- Average of completed orders only
SELECT AVG(
    CASE
        WHEN status = 'Completed' THEN amount
    END
) AS completed_average
FROM orders;

If you use ELSE 0, the zeros from non-matching rows are included in the average and may lower the result. Choose the expression that matches the intended business calculation.

9. Combining MIN() and MAX() with Conditional Logic

The MIN() and MAX() functions can identify the smallest and largest values among records that satisfy a condition.

9.1 Minimum Completed Order

SELECT
    MIN(CASE
        WHEN status = 'Completed' THEN amount
    END) AS smallest_completed_order
FROM orders;

Expected result: 4,500.00

9.2 Maximum Completed Order

SELECT
    MAX(CASE
        WHEN status = 'Completed' THEN amount
    END) AS largest_completed_order
FROM orders;

Expected result: 15,000.00

9.3 Comparing Minimum and Maximum by Status

SELECT
    MIN(CASE
        WHEN status = 'Completed' THEN amount
    END) AS min_completed,
    MAX(CASE
        WHEN status = 'Completed' THEN amount
    END) AS max_completed,
    MIN(CASE
        WHEN status = 'Pending' THEN amount
    END) AS min_pending,
    MAX(CASE
        WHEN status = 'Pending' THEN amount
    END) AS max_pending
FROM orders;

This approach can help identify the range of order values for different categories in a single report.

10. Using IF() Inside Aggregate Functions

MySQL's IF() function can be used inside aggregate functions for straightforward conditions. It is an alternative to some CASE expressions.

10.1 Conditional SUM() with IF()

SELECT
    SUM(IF(status = 'Completed', amount, 0)) AS completed_sales
FROM orders;

This expression adds the order amount when the status is Completed and adds zero for other statuses.

10.2 Conditional COUNT() with IF()

SELECT
    COUNT(IF(status = 'Completed', 1, NULL)) AS completed_orders
FROM orders;

The expression returns 1 for completed orders and NULL for other records. COUNT() counts the non-NULL results.

10.3 CASE Versus IF()

11. Combining Conditional Aggregation with GROUP BY

The GROUP BY clause divides records into groups. Aggregate functions then calculate results for each group. Conditional aggregation can calculate several metrics within every group.

11.1 Sales by Region and Status

SELECT
    region,
    SUM(amount) AS total_sales,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales,
    SUM(CASE
        WHEN status = 'Pending' THEN amount
        ELSE 0
    END) AS pending_sales,
    SUM(CASE
        WHEN status = 'Cancelled' THEN amount
        ELSE 0
    END) AS cancelled_sales
FROM orders
GROUP BY region
ORDER BY region;

This query groups records by region and calculates total sales and status-specific sales for each region.

11.2 Counting Statuses by Region

SELECT
    region,
    COUNT(*) AS total_orders,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders,
    COUNT(CASE
        WHEN status = 'Pending' THEN 1
    END) AS pending_orders,
    COUNT(CASE
        WHEN status = 'Cancelled' THEN 1
    END) AS cancelled_orders
FROM orders
GROUP BY region
ORDER BY region;

This query produces a regional order summary with counts for each status.

11.3 Why GROUP BY Matters

• Without GROUP BY, the query produces one summary row for all selected records.
• With GROUP BY region, the query produces one summary row per region.
• Conditional expressions determine which rows contribute to each metric.
• Multiple conditional aggregates can be calculated in the same grouped query.

12. Combining Conditional Aggregation with HAVING

The HAVING clause filters groups after aggregation. It is useful when you want to display only groups whose conditional totals or counts meet a requirement.

12.1 Regions with Completed Sales Above 10,000

SELECT
    region,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales
FROM orders
GROUP BY region
HAVING SUM(CASE
    WHEN status = 'Completed' THEN amount
    ELSE 0
END) > 10000;

Only regions whose completed sales exceed 10,000 are returned.

12.2 Regions with at Least Two Completed Orders

SELECT
    region,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders
FROM orders
GROUP BY region
HAVING COUNT(CASE
    WHEN status = 'Completed' THEN 1
END) >= 2;

This query returns regions with at least two completed orders.

12.3 WHERE Versus HAVING

13. Conditional Aggregation for Employee Reports

Conditional aggregation is not limited to sales. It can also summarize employee information, attendance, salaries, performance ratings, and department-level statistics.

13.1 Create an Employee Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    employment_status VARCHAR(20)
);

13.2 Sample Employee Records

INSERT INTO employees
(employee_id, employee_name, department, salary, employment_status)
VALUES
(1, 'Aarav', 'IT', 60000, 'Active'),
(2, 'Diya', 'HR', 45000, 'Active'),
(3, 'Kabir', 'IT', 75000, 'Active'),
(4, 'Anaya', 'Sales', 50000, 'Inactive'),
(5, 'Ishaan', 'HR', 40000, 'Inactive'),
(6, 'Riya', 'Sales', 65000, 'Active');

13.3 Counting Active and Inactive Employees

SELECT
    COUNT(*) AS total_employees,
    COUNT(CASE
        WHEN employment_status = 'Active' THEN 1
    END) AS active_employees,
    COUNT(CASE
        WHEN employment_status = 'Inactive' THEN 1
    END) AS inactive_employees
FROM employees;

13.4 Calculating Active Employee Salaries by Department

SELECT
    department,
    SUM(salary) AS total_department_salary,
    SUM(CASE
        WHEN employment_status = 'Active' THEN salary
        ELSE 0
    END) AS active_salary_total,
    AVG(CASE
        WHEN employment_status = 'Active' THEN salary
    END) AS active_average_salary
FROM employees
GROUP BY department
ORDER BY department;

This report shows the total salary for each department, the salary total for active employees, and the average salary among active employees.

14. Using Conditional Aggregation for Financial Reports

Financial reports often require several related calculations, such as income, expenses, refunds, outstanding payments, and net totals. Conditional aggregation can place these metrics into one result set.

14.1 Example: Transactions Table

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_type VARCHAR(20),
    amount DECIMAL(10,2)
);

14.2 Calculate Income, Expenses, and Refunds

SELECT
    SUM(CASE
        WHEN transaction_type = 'Income' THEN amount
        ELSE 0
    END) AS total_income,
    SUM(CASE
        WHEN transaction_type = 'Expense' THEN amount
        ELSE 0
    END) AS total_expenses,
    SUM(CASE
        WHEN transaction_type = 'Refund' THEN amount
        ELSE 0
    END) AS total_refunds
FROM transactions;

The query creates separate totals for each transaction type. The correct interpretation of the final net amount depends on how the business records refunds and expenses, so the accounting rules should be clearly defined before calculating profit or cash flow.

15. Creating a Summary Report with Multiple Aggregate Functions

A single query can combine COUNT(), SUM(), AVG(), MIN(), and MAX() with conditional logic to produce a detailed summary.

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_order_amount,
    MIN(amount) AS minimum_order_amount,
    MAX(amount) AS maximum_order_amount,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_order_count,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales,
    AVG(CASE
        WHEN status = 'Completed' THEN amount
    END) AS average_completed_order
FROM orders;

This report combines overall metrics with metrics specific to completed orders. It is useful for management dashboards and routine business reporting.

16. Calculating Percentages with Conditional Aggregation

Conditional aggregation can calculate the proportion of records or sales belonging to a category. The numerator represents the selected category, while the denominator represents the total.

16.1 Percentage of Completed Orders

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders,
    ROUND(
        100.0 * COUNT(CASE
            WHEN status = 'Completed' THEN 1
        END) / NULLIF(COUNT(*), 0),
        2
    ) AS completed_order_percentage
FROM orders;

The query calculates the percentage of orders that are completed. NULLIF(COUNT(*), 0) prevents division by zero if the table contains no rows.

16.2 Percentage of Completed Sales

SELECT
    SUM(amount) AS total_sales,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales,
    ROUND(
        100.0 * SUM(CASE
            WHEN status = 'Completed' THEN amount
            ELSE 0
        END) / NULLIF(SUM(amount), 0),
        2
    ) AS completed_sales_percentage
FROM orders;

This calculation measures completed sales as a percentage of total sales. If amounts can be NULL or negative in your real dataset, define the business rules for those values before interpreting the percentage.

17. Conditional Aggregation and NULL Values

NULL represents a missing or unknown value. Aggregate functions do not all handle NULL in the same way, so conditional expressions must be designed carefully.

17.1 How Aggregate Functions Handle NULL

17.2 Handling NULL in a Conditional SUM()

SELECT
    COALESCE(
        SUM(CASE
            WHEN status = 'Completed' THEN amount
            ELSE 0
        END),
        0
    ) AS completed_sales
FROM orders;

COALESCE() returns the first non-NULL expression. It can be used to display zero when the aggregate result is NULL. However, if a matching order has a NULL amount, that amount is still not a known numeric value; decide whether it should be excluded, treated as zero, or handled separately.

17.3 Handling NULL Status Values

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE
        WHEN status IS NULL THEN 1
    END) AS orders_without_status
FROM orders;

Use IS NULL to test for missing values. A comparison such as status = NULL does not correctly test for NULL.

18. Common Mistakes and Troubleshooting

18.1 Using COUNT() with ELSE 0 Incorrectly

Incorrect for counting only completed orders:

SELECT
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
        ELSE 0
    END) AS completed_orders
FROM orders;

This counts every row because both 1 and 0 are non-NULL values. Use the following version instead:

SELECT
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders
FROM orders;

Alternatively, use SUM(CASE WHEN condition THEN 1 ELSE 0 END) to count matching rows.

18.2 Using ELSE 0 in an Average

If the objective is to average only completed orders, using ELSE 0 incorrectly includes non-completed rows as zeros.

-- Average only the matching rows
SELECT AVG(
    CASE
        WHEN status = 'Completed' THEN amount
    END
) AS average_completed_amount
FROM orders;

18.3 Forgetting GROUP BY

If a report requires one result per region or department, include the appropriate GROUP BY clause. Without it, aggregate functions summarize the selected rows as a whole.

18.4 Using the Wrong Condition

Check spelling, capitalization behavior under the column's collation, numeric thresholds, and category values. For example, a condition for 'Complete' will not match rows stored as 'Completed'.

18.5 Mixing Row Filters and Group Filters

Use WHERE to filter individual records before aggregation and HAVING to filter grouped results. Choosing the wrong clause can change the report's meaning.

18.6 Ignoring NULL Values

Decide how missing amounts, statuses, and other values should be handled. NULL is not automatically equivalent to zero or an empty string.

18.7 Repeating Complex Conditions

When the same condition appears in many expressions, check that every expression uses the intended rule. For complicated reports, consider using a common table expression or derived table to make the logic easier to maintain.

19. Best Practices for Conditional Aggregation

• Use descriptive aliases such as completed_sales and pending_orders.
• Use CASE when conditions involve several categories or business rules.
• Use IF() for simple two-outcome expressions when it improves readability.
• Use COUNT(CASE WHEN condition THEN 1 END) to count matching rows.
• Use SUM(CASE WHEN condition THEN amount ELSE 0 END) to total values conditionally.
• For conditional averages, omit ELSE 0 when non-matching rows should not affect the average.
• Use GROUP BY when results are required separately for regions, departments, months, or categories.
• Use HAVING to filter groups based on aggregate results.
• Use NULLIF() to protect percentage calculations from division by zero.
• Test queries with known sample data and verify totals independently.
• Use suitable indexes for filtering and joining when working with large datasets, and examine execution plans when performance matters.
• Keep business definitions consistent, especially for refunds, cancelled transactions, missing values, and revenue totals.

20. Practice Exercises

Exercise 1: Count Completed Orders

Write a query to count only orders whose status is Completed.

SELECT
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders
FROM orders;

Exercise 2: Calculate Pending Sales

Write a query to calculate the total amount of pending orders.

SELECT
    SUM(CASE
        WHEN status = 'Pending' THEN amount
        ELSE 0
    END) AS pending_sales
FROM orders;

Exercise 3: Calculate Average Cancelled Order Amount

Write a query to calculate the average amount for cancelled orders only.

SELECT
    AVG(CASE
        WHEN status = 'Cancelled' THEN amount
    END) AS average_cancelled_amount
FROM orders;

Exercise 4: Summarize Sales by Region

Display total sales and completed sales for each region.

SELECT
    region,
    SUM(amount) AS total_sales,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales
FROM orders
GROUP BY region;

Exercise 5: Calculate the Completed Order Percentage

Calculate the percentage of orders that have been completed.

SELECT
    ROUND(
        100.0 * COUNT(CASE
            WHEN status = 'Completed' THEN 1
        END) / NULLIF(COUNT(*), 0),
        2
    ) AS completed_percentage
FROM orders;

Exercise 6: Find the Largest Completed Order

Display the largest amount among completed orders.

SELECT
    MAX(CASE
        WHEN status = 'Completed' THEN amount
    END) AS largest_completed_order
FROM orders;

21. Mini Project: Order Performance Summary

Project Objective: Create a MySQL report that summarizes order performance across regions, including order counts, total sales, completed sales, pending sales, cancelled sales, and the completed-order percentage.

21.1 Project Requirements

• Display one row for each region.
• Calculate the total number of orders.
• Count completed, pending, and cancelled orders separately.
• Calculate total sales and sales for each status.
• Calculate the percentage of completed orders within each region.
• Sort the report by total sales in descending order.

21.2 Project Solution

SELECT
    region,
    COUNT(*) AS total_orders,
    COUNT(CASE
        WHEN status = 'Completed' THEN 1
    END) AS completed_orders,
    COUNT(CASE
        WHEN status = 'Pending' THEN 1
    END) AS pending_orders,
    COUNT(CASE
        WHEN status = 'Cancelled' THEN 1
    END) AS cancelled_orders,
    SUM(amount) AS total_sales,
    SUM(CASE
        WHEN status = 'Completed' THEN amount
        ELSE 0
    END) AS completed_sales,
    SUM(CASE
        WHEN status = 'Pending' THEN amount
        ELSE 0
    END) AS pending_sales,
    SUM(CASE
        WHEN status = 'Cancelled' THEN amount
        ELSE 0
    END) AS cancelled_sales,
    ROUND(
        100.0 * COUNT(CASE
            WHEN status = 'Completed' THEN 1
        END) / NULLIF(COUNT(*), 0),
        2
    ) AS completed_order_percentage
FROM orders
GROUP BY region
ORDER BY total_sales DESC;

21.3 Project Learning Outcomes

• Combine several aggregate functions in one query.
• Apply conditions inside aggregate expressions.
• Generate grouped business reports.
• Calculate percentages safely.
• Interpret summary results for decision-making.

22. Interview Questions and Answers

Q1. What is conditional aggregation in MySQL?

Answer: Conditional aggregation combines aggregate functions such as SUM(), COUNT(), or AVG() with expressions such as CASE or IF() to calculate summaries for selected records.

Q2. Why combine CASE with SUM()?

Answer: It allows a query to add values only when a condition is satisfied, such as calculating total revenue from completed orders.

Q3. How do you count rows that meet a condition?

Answer: Use COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN 1 ELSE 0 END).

Q4. Why should ELSE 0 be avoided in some COUNT(CASE) expressions?

Answer: Because COUNT() counts every non-NULL result, including zero. Omitting ELSE makes non-matching rows return NULL, which is not counted.

Q5. When should ELSE 0 be used with SUM()?

Answer: It is commonly used when non-matching rows should contribute zero to a conditional total.

Q6. Why might ELSE 0 be inappropriate inside AVG()?

Answer: Non-matching rows would contribute zero to the average and could lower the result. Omit the ELSE branch when only matching values should be averaged.

Q7. What is the difference between CASE and IF()?

Answer: CASE supports multiple conditions and is useful for complex rules. IF() is a compact function for a single condition with two possible outcomes.

Q8. Can conditional aggregation be used with GROUP BY?

Answer: Yes. It can calculate conditional totals and counts separately for each group, such as each department or region.

Q9. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping, while HAVING filters groups after aggregate calculations.

Q10. How can you calculate the percentage of completed orders?

Answer: Divide the conditional count of completed orders by the total row count, multiply by 100, and use NULLIF() to avoid division by zero.

Q11. What happens when SUM() receives only NULL values?

Answer: It returns NULL when there are no non-NULL input values. COALESCE() can be used to provide a default result when appropriate.

Q12. Can multiple conditional aggregates appear in one SELECT statement?

Answer: Yes. A single query can include multiple conditional counts, sums, averages, minimums, and maximums.

23. Quick Revision Checklist

• Aggregate functions summarize values across multiple rows.
• CASE evaluates conditions and returns the appropriate result.
• IF() handles a simple condition with two outcomes.
• SUM(CASE ...) calculates conditional totals.
• COUNT(CASE WHEN condition THEN 1 END) counts matching records.
• AVG(CASE ...) calculates an average for matching records.
• MIN(CASE ...) and MAX(CASE ...) find conditional minimums and maximums.
• GROUP BY produces a summary for each group.
• HAVING filters groups based on aggregate conditions.
• NULL handling can change aggregate results.
• NULLIF() can prevent division by zero in percentage calculations.
• Always verify whether non-matching records should contribute zero or be excluded.

24. Conclusion

Combining aggregate functions with conditional logic is an important MySQL skill for building flexible and meaningful reports. By using CASE or IF() inside functions such as SUM(), COUNT(), AVG(), MIN(), and MAX(), you can calculate several category-specific metrics within one query.

When conditional aggregation is combined with GROUP BY, HAVING, and careful NULL handling, it becomes useful for sales analysis, employee reporting, financial summaries, and dashboard development. Practice these patterns with sample tables and verify each result to build confidence in writing accurate MySQL queries.

Continue Learning: Explore the MySQL Training Course to develop your SQL skills, and use the course demo registration page to learn more about the training.